package www.lagou.app;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;
import www.lagou.entity.Employee;
import www.lagou.entity.Phone;
import www.lagou.utils.DateUtils;
import www.lagou.utils.DruidUtils;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

/**
 * @author 李树飞
 */
public class Test01 {

    /**
     * 第一题转账
     */
    //实现卡号：1122334455向55443332211转账5000元的操作；
    //步骤
    //a) 使用连接池创建QueryRunner对象；
    //b) 判断转出方是否有足够余额，如果不足，提示信息：”余额不足！”，并结束程序；
    //c) 通过卡号 进行转账的操作；
    //d) 转账结束后, 将转入、转出记录分别写入到Transaction表中。
    @Test
    public void traAccount() /*throws SQLException */{
        QueryRunner qr = new QueryRunner();
        //执行语句
        String sql;

        //获取连接
        Connection connection = DruidUtils.getConnection();
        try {
            //设置事务为手动提交
            connection.setAutoCommit(false);

            //获取余额
            sql= "select balance from account where card = ?";
            Double balance = qr.query(connection,sql, new ScalarHandler<>(), "1122334455");

            //判断是否>=5000
            if (balance >= 5000) {
                //转出操作
                sql= "update account set balance = balance - ? where card = ?";
                qr.update(connection,sql, 5000,"1122334455");

                //记录转出信息
                sql= "insert into transaction values(?,?,?,?,?)";
                Object[] param = {null,"1122334455"," 转出",5000.0, DateUtils.getDateFormart()};
                qr.update(connection,sql,param);
                System.out.println("转出成功");

                //转入操作
                sql= "update account set balance = balance + ? where card = ?";
                qr.update(connection,sql, 5000,"55443332211");

                //记录转入信息
                sql= "insert into transaction values(?,?,?,?,?)";
                Object[] param1 = {null,"55443332211"," 转入",5000.0, DateUtils.getDateFormart()};
                qr.update(connection,sql, param1);
                System.out.println("转入成功");
            }else {
                System.out.println("余额不足");
            }

            //提交事务
            connection.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                //发生异常，回滚
                connection.rollback();
            } catch (SQLException e) {
                e.printStackTrace();
                System.out.println("转账失败!");
            }
        }
    }

    /**
     * 第二题，手机
     */
    //需求1:  查询价格高于2000元，生产日期是2019年之前的所有手机
    @Test
    public void findByPriceDate() throws SQLException {
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
        //执行语句
        String sql = "select * from phone where price > ? and prodate < ?";
        List<Phone> phoneList = qr.query(sql, new BeanListHandler<>(Phone.class), 2000, "2019/01/01");
        for (Phone phone : phoneList) {
            System.out.println(phone);
        }
    }

    //需求2:  查询所有颜色是白色的手机信息
    @Test
    public void findByColor() throws SQLException {
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
        //执行语句
        String sql = "select * from phone where color = ?";
        List<Phone> phoneList = qr.query(sql, new BeanListHandler<>(Phone.class), "白色");
        for (Phone phone : phoneList) {
            System.out.println(phone);
        }
    }


    /**
     * 第三题，员工部门
     */
    //需求1: 查询所有的员工信息 (不包含没有部门的员工)
    @Test
    public void findAll() throws SQLException {
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
        //执行语句
        String sql = "select * from employee where did is not null";
        List<Employee> employeeList = qr.query(sql, new BeanListHandler<>(Employee.class));
        for (Employee employee : employeeList) {
            System.out.println(employee);
        }
    }

    //需求2: 查询每个员工的 姓名, 薪资 和 所属部门名称
    @Test
    public void find() throws SQLException {
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
        //执行语句
        String sql = "select * from employee";
        List<Employee> employeeList = qr.query(sql, new BeanListHandler<>(Employee.class));

        for (Employee employee : employeeList) {
            System.out.print("员工姓名：" + employee.getName() + "  ");
            System.out.print("员工薪资：" + employee.getSalary() + "  ");
            System.out.println("员工所属部门：" + findDeptById(employee.getDid()));
        }
    }

    //用Test 跑单元测试是要求方法不能有参数和返回类型
    public String findDeptById(int id) throws SQLException {
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
        //执行语句
        String sql = "select deptname from dept where id = ?";
        return qr.query(sql, new ScalarHandler<>(),id);
    }
}
